﻿. "$PSScriptRoot\MaintenanceWindows.ps1"

Function New-ScheduledSQLReportSubscription
{
param
(
    [string]$ReportToExecute = "States 1 - Enforcement states for a deployment",
    [Parameter(Mandatory=$true)][string]$DescriptionOFScheduledReport,
    [Parameter(Mandatory=$true)][string]$scheduleXml,
    #Email addresses seperated by ; in one string
    [Parameter(Mandatory=$true)][string]$SendSQLReportToEmailAddresses,
    [Parameter(Mandatory=$true)][string]$DeploymentId,
    [Parameter(Mandatory=$true)][string]$Subject,
    [Parameter(Mandatory=$true)][string]$SQLReportingURL,
    [Parameter(Mandatory=$true)][string]$ReportingServer
)
        
    $wsProxy = New-WebServiceProxy -Uri $SQLReportingURL -UseDefaultCredential   
    $wsProxyNamespace = $wsProxy.gettype().namespace
    $extSettings = new-object -TypeName "$wsProxyNamespace.ExtensionSettings"
    $ParameterValue = new-object -TypeName "$wsProxyNamespace.ParameterValue"

    $catalogitemsarray = $wsProxy.ListChildren("/", $true);
    $reportpath = $catalogitemsarray | where name -EQ $ReportToExecute | select Path

    $reportpathwithoutrdl = "http://$ReportingServer"
    $reportpathwithoutrdl += $reportpath.Path

    $report = "http://$ReportingServer"
    $report += $reportpath.Path
    $report += ".rdl"

    $desc = $DescriptionOFScheduledReport #"Send to Document Library"
    $eventType = "TimedSubscription"
        
    #$ParameterValue
    $extensionParams1 = new-object "$wsProxyNamespace.ParameterValue"
    $extensionParams1.Name ="RenderFormat"
    $extensionParams1.Value ="EXCELOPENXML" #"MHTML"

    $extensionParams2 = new-object "$wsProxyNamespace.ParameterValue"
    $extensionParams2.Name ="DEPLOYMENTID"
    $extensionParams2.Value = $DeploymentId #"{D9EA6846-47EA-40A9-A54F-06D7AEE3370D}"#"16779582"

    $extensionParams3 = new-object "$wsProxyNamespace.ParameterValue"
    $extensionParams3.Name ="ItemPath"
    $extensionParams3.Value =$reportpath.Path

    $extensionParams4 = new-object "$wsProxyNamespace.ParameterValue"
    $extensionParams4.Name = "Subject"
    $extensionParams4.Value = $Subject

    $extensionParams5 = new-object "$wsProxyNamespace.ParameterValue"
    $extensionParams5.Name = "WRITEMODE"
    $extensionParams5.Value = "Overwrite"

    $extensionParams6 = new-object "$wsProxyNamespace.ParameterValue"
    $extensionParams6.Name = "IncludeReport"
    $extensionParams6.Value = "True"

    #ToEmailAddressesID
    $extensionParams7 = new-object "$wsProxyNamespace.ParameterValue"
    $extensionParams7.Name = "TO"
    $extensionParams7.Value = $SendSQLReportToEmailAddresses

    $ParameterValue = ($extensionParams1,$extensionParams4,$extensionParams6,$extensionParams7)#$extensionParams2,$extensionParams3
    #$ParameterValue = ($extensionParams1,$extensionParams2,$extensionParams3,$extensionParams4,$extensionParams5,$extensionParams6,$extensionParams7)

    $matchData = $scheduleXml
        
    $extSettings.ParameterValues = $ParameterValue
    $extSettings.Extension = "Report Server Email"

    #$Global:wsProxy.CreateSubscription($report, $extSettings, $desc, $eventType, $matchData, $test3)
    $wsProxy.CreateSubscription($reportpath.Path, $extSettings, $desc, $eventType, $matchData, $extensionParams2) #


}

Function New-ScheduledSQLReportSubscriptions
{
param
(
    [Parameter(Mandatory=$true)][xml]$xmlDocumentWithConfiguration,
    [Parameter(Mandatory=$true)]$SoftwareUpdateDeployment,
    [Parameter(Mandatory=$true)][string]$DeploymentId,
    [Parameter(Mandatory=$true)][string]$ChangeOrderNumber,
    #if any unique email addresses or it will be retrived from default email and explicit for each report
    [Parameter(Mandatory=$false)][string]$SendSQLReportToEmailAddresses,
    [datetime]$DateTimeToDisplayAfterDescriptionOnReport
)

    $Subject = ("Change Order $ChangeOrderNumber @ReportName was executed at @ExecutionTime")

    if($SQLReportEmailAddresses -eq $null)
    {
        $SQLReportEmailAddresses = $xmlDocumentWithConfiguration.ADRs.Common.SendSQLReportToEmailAddresses
    }

    $Reports = $SoftwareUpdateDeployment.Reports.Report
    foreach($Report in $Reports)
    {
        $MaintenanceWindow = $SoftwareUpdateDeployment.MaintenanceWindows.MaintenanceWindow | where Name -eq ($Report.MaintenanceWindowToExecuteReportAfter)
        $Schedule = Create-CMSchedule -CMScheduleParameter $MaintenanceWindow -Plus1Month ([System.Convert]::ToBoolean($SoftwareUpdateDeployment.Plus1Month))

        $StartDateTime = $Schedule.StartTime.AddHours($Schedule.HourDuration+([int]$Report.NumberOfHoursAfterMWHasEndedShouldReportExecute))
        
        $ScheduleDefinition = $Report.XMLScheduleDefinition.InnerXml.Replace("<StartDateTime>0</StartDateTime>","<StartDateTime>$StartDateTime</StartDateTime>")

        if($DateTimeToDisplayAfterDescriptionOnReport -eq $null)
        {
            $DateTimeToDisplayAfterDescriptionOnReport = Get-Date
        }
        #Delete any old subscription
        Delete-ScheduledReportSubscription -ScheduledReportToFind ($Report.NameOfReport +" - " + $SoftwareUpdateDeployment.Name + " - " + (($DateTimeToDisplayAfterDescriptionOnReport).Year) + " " + (($DateTimeToDisplayAfterDescriptionOnReport).Month) ) -SQLReportingURL ($xmlDocumentWithConfiguration.ADRs.Common.SQLReportingURL) -ReportingServer ($xmlDocumentWithConfiguration.ADRs.Common.ReportingServer)
        
        #add new sql report subscription
        New-ScheduledSQLReportSubscription -DescriptionOFScheduledReport ($Report.NameOfReport +" - " + $SoftwareUpdateDeployment.Name + " - " + (($DateTimeToDisplayAfterDescriptionOnReport).Year) + " " + (($DateTimeToDisplayAfterDescriptionOnReport).Month) ) -DeploymentId $DeploymentId -Subject $Subject -SendSQLReportToEmailAddresses ($SQLReportEmailAddresses + $Report.SendSQLReportToEmailAddresses) -scheduleXml $ScheduleDefinition -SQLReportingURL ($xmlDocumentWithConfiguration.ADRs.Common.SQLReportingURL) -ReportingServer ($xmlDocumentWithConfiguration.ADRs.Common.ReportingServer)
    }
    


}

Function Get-ScheduledReportSubscription
{
param
(
    [string]$ReportToExecute = "States 1 - Enforcement states for a deployment",
    [string]$ScheduledReportToFind,
    [Parameter(Mandatory=$true)][string]$ReportingServer
)

    $wsProxy = New-WebServiceProxy -Uri $SQLReportingURL -UseDefaultCredential   
    $wsProxyNamespace = $wsProxy.gettype().namespace# [string]$Global:wsProxy       
    $extSettings = new-object -TypeName "$wsProxyNamespace.ExtensionSettings"
    $ParameterValue = new-object -TypeName "$wsProxyNamespace.ParameterValue"

    $catalogitemsarray = $wsProxy.ListChildren("/", $true);
    $reportpath = $catalogitemsarray | where name -EQ $ReportToExecute | select Path

    $reportpathwithoutrdl = "http://$ReportingServer"
    $reportpathwithoutrdl += $reportpath.Path

    $report = "http://$ReportingServer"
    $report += $reportpath.Path
    $report += ".rdl"

    #$sub = $wsProxy.ListSubscriptions($reportpath.Path)

    $Subscription = $wsProxy.ListSubscriptions($reportpath.Path) | where Description -eq $ScheduledReportToFind
    $Subscription
   
}

Function Delete-ScheduledReportSubscription
{
param
(
    [Parameter(Mandatory=$true)][string]$ScheduledReportToFind,
    [Parameter(Mandatory=$true)][string]$SQLReportingURL,
    [Parameter(Mandatory=$true)][string]$ReportingServer
)
    $Subscriptions = Get-ScheduledReportSubscription -ScheduledReportToFind $ScheduledReportToFind -ReportingServer $ReportingServer
    $wsProxy = New-WebServiceProxy -Uri $SQLReportingURL -UseDefaultCredential   

    foreach($Subscription in $Subscriptions)
    {
        $wsProxy.DeleteSubscription($Subscription.SubscriptionID)
    }
}

